//===============================================================================
// Microsoft Architecture Strategy Team
// LitwareHR - SaaS Sample Application
//===============================================================================
// Copyright  Microsoft Corporation.  All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
// FITNESS FOR A PARTICULAR PURPOSE.
//===============================================================================
// The example companies, organizations, products, domain names,
// e-mail addresses, logos, people, places, and events depicted
// herein are fictitious.  No association with any real company,
// organization, product, domain name, email address, logo, person,
// places, or events is intended or should be inferred.
//===============================================================================


//===============================================================================
// Microsoft Architecture Strategy Team
// LitwareHR - S+S Sample Application
//===============================================================================
// Copyright  Microsoft Corporation.  All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
// FITNESS FOR A PARTICULAR PURPOSE.
//===============================================================================
// The example companies, organizations, products, domain names,
// e-mail addresses, logos, people, places, and events depicted
// herein are fictitious.  No association with any real company,
// organization, product, domain name, email address, logo, person,
// places, or events is intended or should be inferred.
//===============================================================================

using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.SqlCe;
using Microsoft.Practices.ObjectBuilder;

using System.Xml;
using System.Xml.Serialization;

using System.IO;

using Shp.Runtime.Contract;
using System.Diagnostics;
using System.Transactions;
using System.Globalization;
using System.Collections.ObjectModel;

namespace LitwareHR.PublicApp.WinUX.Services
{
    /// <summary>
    /// DBService encapsulates all interactions with the local database for
    /// LitwareHRSC 
    /// </summary>
    public class DBService
    {
        Database db = null;

        private delegate string ColumnSQLSubString(EntityField field); 

        
        [InjectionConstructor]
        public DBService()
        {
             db = DatabaseFactory.CreateDatabase();
        }

        /// <summary>
        /// Eliminates View's definitions stored in the Database
        /// </summary>
        /// <param name="viewName">The name of teh view to eliminate</param>
        public void RemoveViewDef(string viewName)
        {
            string deleteSQL = string.Format("Delete FROM viewDef WHERE viewName = '{0}'", viewName);
            db.ExecuteNonQuery(CommandType.Text, deleteSQL);
        }

        /// <summary>
        /// Adds a View definition to the database
        /// </summary>
        /// <param name="viewName">Name fo the View</param>
        /// <param name="viewDef">Serialized content of the View Definition</param>
        public void CreateViewDef(string viewName, string viewDef)
        {
            string insertSQL = string.Format("Insert Into viewDef Values ('{0}', '{1}')", viewName, viewDef);
            int r = db.ExecuteNonQuery(CommandType.Text, insertSQL);
        }

        /// <summary>
        /// Returns the serialized definition of a View
        /// </summary>
        /// <param name="viewName">Name of the View to get</param>
        /// <returns></returns>
        public string GetViewDef(string viewName)
        {
            string selectSQL = string.Format("Select viewDef FROM viewDef WHERE viewName = '{0}'", viewName);
            return (string)db.ExecuteScalar(CommandType.Text, selectSQL);
        }

        /// <summary>
        /// Deletes an Entity definition
        /// </summary>
        /// <param name="entityName">The name of the entity to eliminate (like "position")</param>
        public void DeleteEntityDef(string entityName)
        {
            // Delete any instance rows (there should be 1)
            string deleteSQL = string.Format("Delete FROM entityDef WHERE entityName = '{0}'", entityName);
            db.ExecuteNonQuery(CommandType.Text, deleteSQL);

            // Delete any metadata
            string selectSQL = string.Format("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}'", entityName.Trim());
            int qty = (int)db.ExecuteScalar(CommandType.Text, selectSQL);
            if( qty > 0 )
            {  
                string dropSQL = string.Format("DROP TABLE [{0}]", entityName);
                db.ExecuteNonQuery(CommandType.Text, dropSQL);
            }
        }
        
        /// <summary>
        /// Creates a new Entity Definition
        /// </summary>
        /// <param name="entityName">Name of the Entity to create</param>
        /// <param name="entityDef">Serialized definition of the entity's fields</param>
        /// <param name="entityFlds">Array of fields</param>
        public void CreateEntityDef(string entityName, string entityDef, EntityField[] entityFlds)
        {
            string insertSQL = string.Format("Insert Into entityDef Values ('{0}', '{1}')", entityName, entityDef);
            int r = db.ExecuteNonQuery(CommandType.Text, insertSQL);

            CreateEntityTable(entityName, entityFlds);
        }

        /// <summary>
        /// Creates the instance table. This will store the entity instances
        /// </summary>
        /// <param name="entityName">Name of the entity (Like:'position') "</param>
        /// <param name="entityFlds">Array of fields for this Entity</param>
        private void CreateEntityTable(string entityName, EntityField[] entityFlds)
        {
            String createSQL = String.Empty;
            StringBuilder strBld = new StringBuilder();
            strBld.Append(string.Format("CREATE TABLE [{0}] (", entityName));
            strBld.Append("[id] uniqueidentifier, ");

            //Iterate on each field an get the DDL for it
            foreach (EntityField fld in entityFlds)
            {
                strBld.Append(string.Format("[{0}] {1},", fld.Name, GetSQLType(fld.TypeName)));
            }

            //Eliminate the last ","
            createSQL = strBld.ToString(0, strBld.Length - 1) + ")";
            
            db.ExecuteNonQuery(CommandType.Text, createSQL);
        }               

        /// <summary>
        /// Returns the serialized definition of the Entity in serialized form
        /// </summary>
        /// <param name="entityName"></param>
        /// <returns></returns>
        public string GetEntityDef(string entityName)
        {
            string selectSQL = string.Format("Select EntityDef FROM entityDef WHERE EntityName = '{0}'", entityName);
            return (string)db.ExecuteScalar(CommandType.Text, selectSQL);
        }

        /// <summary>
        /// Helper to translate from "LitwareHR" types and the CLR
        /// </summary>
        /// <param name="typeName"></param>
        /// <returns></returns>
        private Type GetFieldType(string typeName)
        {
            if (typeName.Contains("Date"))
                return typeof(DateTime);

            if (typeName.Contains("Short text"))
                return typeof(String);

            if (typeName.Contains("Long text"))
                return typeof(String);

            if (typeName.Contains("Boolean"))
                return typeof(Boolean);

            if (typeName.Contains("Number"))
                return typeof(Decimal);

            return typeof(Object);
        }

        /// <summary>
        /// Helper to translate from "LitwareHR types" and SQL ones
        /// </summary>
        /// <param name="typeName"></param>
        /// <returns></returns>
        private string GetSQLType(string typeName)
        {
            if (typeName.Contains("Date"))
                return "DateTime";

            if (typeName.Contains("Short String"))
                return "nvarchar(255)";

            if (typeName.Contains("Long String"))
                return "nvarchar(4000)";

            if (typeName.Contains("Boolean"))
                return "bit";

            if (typeName.Contains("Number"))
                return "float";

            // Default for now is a nvarchar(4000)
            return "nvarchar(4000)";
        }

        /// <summary>
        /// Returns entities instances for a given type, filtered by key
        /// </summary>
        /// <param name="entityName">Name of the entity</param>
        /// <param name="keyName">Key to filter</param>
        /// <param name="keyValue">Key value to compare against</param>
        /// <returns></returns>
        public DataTable GetEntities(string entityName, string keyName, string keyValue)
        {
            string selectSQL = string.Format("Select * FROM [{0}] WHERE {1} = {2}", entityName, keyName, keyValue);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, selectSQL);
            if (ds != null)
            {
                if (ds.Tables.Count == 1)
                {
                    return ds.Tables[0];
                }
            }
            return null;
        }

        /// <summary>
        /// Return all Entity instances for a given one
        /// </summary>
        /// <param name="entityName">Name of the entity</param>
        /// <returns></returns>
        public DataTable GetAllEntities(string entityName)
        {
            string selectSQl = string.Format("Select * FROM [{0}]", entityName);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, selectSQl);
            if (ds != null)
            {
                if (ds.Tables.Count == 1)
                {
                    return ds.Tables[0];
                }
            }
            return null;
        }

        public void UpdateEntityTable(string entityName, string keyName, StringPair[][] entityInstanceDef)
        {
            using (TransactionScope tranScope = new TransactionScope(TransactionScopeOption.Suppress))
            {
                DataTable dt = GetAllEntities(entityName);
                
                foreach (StringPair[] entity in entityInstanceDef)
                {
                    AddRow(dt, entity);
                }

                DataRow[] newRows = dt.Select("", "", DataViewRowState.Added);
                if (newRows.Length > 0)
                {
                    string commandText = "Insert INTO [{0}] ({1}) VALUES ({2})";
                    StringBuilder fields = new StringBuilder();
                    StringBuilder values = new StringBuilder();

                    foreach (DataColumn col in dt.Columns)
                    {
                        fields.AppendFormat("[{0}],", col.ColumnName);
                        values.AppendFormat("@{0},", col.ColumnName.Replace(" ", ""));
                    }

                    string fieldsStr = fields.ToString();
                    string valuesStr = values.ToString();

                    commandText = string.Format(CultureInfo.InvariantCulture
                                              , commandText
                                              , entityName
                                              , fieldsStr.Substring(0, fieldsStr.Length - 1)
                                              , valuesStr.Substring(0, valuesStr.Length -1));

                    foreach (DataRow newRow in newRows)
                    {
                        DbCommand command = db.GetSqlStringCommand(commandText);

                        foreach (DataColumn col in dt.Columns)
                        {
                            DbParameter parameter = command.CreateParameter();
                            parameter.ParameterName = String.Format(CultureInfo.InvariantCulture, "@{0}", col.ColumnName.Replace(" ", ""));
                            parameter.Value = newRow[col.ColumnName];
                            command.Parameters.Add(parameter);
                        }

                        db.ExecuteNonQuery(command);
                    }
                }               
            }
        }
       
        private void GetDataFromEntityInstance(DataTable table,  StringPair[][] entityInstanceDef)
        {
            foreach (StringPair[] entity in entityInstanceDef)
            {
                AddRow(table, entity);
            }
        }

        private void AddRow(DataTable table, StringPair[] entity)
        {
            DataRow dr = table.NewRow();
            foreach (DataColumn col in table.Columns)
            {
                if (!StringPair.ContainsKey(col.ColumnName, entity))
                    continue;

                if (col.DataType == typeof(Boolean))
                {
                    Boolean flag;
                    if (Boolean.TryParse(StringPair.Get(col.ColumnName, entity).Value, out flag))
                        dr[col.ColumnName] = flag;
                    else
                        dr[col.ColumnName] = DBNull.Value;
                }
                else if (col.DataType == typeof(Double))
                {
                    Decimal value;
                    if (Decimal.TryParse(StringPair.Get(col.ColumnName, entity).Value, out value))
                        dr[col.ColumnName] = value;
                    else
                        dr[col.ColumnName] = DBNull.Value;
                }
                else
                    dr[col.ColumnName] = StringPair.Get(col.ColumnName, entity).Value; 
            }
            table.Rows.Add(dr);
        }

        private void UpdateRow(DataRow row, StringPair[] entity)
        {
            DataTable table = row.Table;
            foreach (DataColumn col in table.Columns)
            {
                if (StringPair.Get(col.ColumnName, entity) != null)
                    if (row[col.ColumnName].ToString() != StringPair.Get(col.ColumnName, entity).Value)
                        row[col.ColumnName] = StringPair.Get(col.ColumnName, entity).Value;
            }
        }

        /// <summary>
        /// Add a generic instance to the DB
        /// </summary>
        /// <param name="entityName">Name of the Entity</param>
        /// <param name="list">List of pairs Field/Value</param>
        public void AddInstance(string entityName, EntityField[] list)
        {
            string InsertSQLValues = "";
            string InsertSQL = string.Format("Insert INTO [{0}] (", entityName);
            
            foreach (EntityField field in list)
            {
                InsertSQL += "[" + field.Name + "],";
                InsertSQLValues += string.Format("'{0}'", field.Value) + ",";
            }

            //Eliminate last ","
            InsertSQL = InsertSQL.Substring(0, InsertSQL.Length - 1);
            //Eliminate last ","
            InsertSQLValues = InsertSQLValues.Substring(0, InsertSQLValues.Length - 1);

            //Add values
            InsertSQL += " ) Values (" + InsertSQLValues + ")";

            db.ExecuteNonQuery(CommandType.Text, InsertSQL); 
        }

        /// <summary>
        /// This method will update the local Entity Def after checking the differnce in Local and Server Entity Diff
        /// </summary>
        /// <param name="localEntityDef"></param>
        /// <param name="entityDef"></param>
        /// <param name="entityName"></param>
        public void AlterEntityDef(EntityField[] localEntityDef, EntityField[] currentEntityDef, string entityName, string currentEntityDefStr)
        {
            string alterTableDropColumnFormat = "ALTER TABLE {0} DROP COLUMN {1}";
            string alterTableAddColumnFormat = "ALTER TABLE {0} ADD {1}";
            string updateEntityDefFormat = "UPDATE entityDef SET entityDef = '{0}' WHERE entityName = '{1}'";
            
            List<string> sqlString = new List<string>();

            //SQL String to update Entity Definition
            sqlString.Add(string.Format(updateEntityDefFormat, currentEntityDefStr, entityName));

            //SQl String to drop columns
            List<EntityField> dropedEntityFieldDefList = GetDifference(localEntityDef, currentEntityDef);
            CreateAlterColumnSQL(entityName, alterTableDropColumnFormat, sqlString, dropedEntityFieldDefList, DropColumnSQLSubString);

            //SQL String to add columns
            List<EntityField> addedEntityFieldDefList = GetDifference(currentEntityDef, localEntityDef);
            CreateAlterColumnSQL(entityName, alterTableAddColumnFormat, sqlString, addedEntityFieldDefList, AddColumnSQLSubString);

            ExecuteSQLBatch(sqlString);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlString"></param>
        private void ExecuteSQLBatch(List<string> sqlString)
        {
            using (TransactionScope tranScope = new TransactionScope(TransactionScopeOption.Suppress))
            {
                foreach (string sql in sqlString)
                    db.ExecuteNonQuery(CommandType.Text, sql);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entityName"></param>
        /// <param name="alterTableColumnFormat"></param>
        /// <param name="sqlString"></param>
        /// <param name="entityFieldDefList"></param>
        /// <param name="GetColumnSQlSubString"></param>
        private void CreateAlterColumnSQL(string entityName, 
                                          string alterTableColumnFormat, 
                                          List<string> sqlString, 
                                          List<EntityField> entityFieldDefList,
                                          ColumnSQLSubString GetColumnSQlSubString)
        {
            if (entityFieldDefList.Count > 0)
            {
                StringBuilder alterCoulmnSQL = new StringBuilder();
                foreach (EntityField field in entityFieldDefList)
                {
                    alterCoulmnSQL.Append(GetColumnSQlSubString(field));
                }
                alterCoulmnSQL.Remove(alterCoulmnSQL.Length - 1, 1);
                sqlString.Add(string.Format(alterTableColumnFormat, entityName, alterCoulmnSQL.ToString()));
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="field"></param>
        /// <returns></returns>
        private string AddColumnSQLSubString(EntityField field)
        {
            return "[" + field.Name + "] " + GetSQLType(field.TypeName) + ",";
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="field"></param>
        /// <returns></returns>
        private string DropColumnSQLSubString(EntityField field)
        {
            return "[" + field.Name + "],";
        }
                
        /// <summary>
        /// This functions returns true if Entiry is Missing (not present) in Defination
        /// </summary>
        /// <param name="entityField"></param>
        /// <param name="entityDef"></param>
        /// <returns></returns>
        private List<EntityField> GetDifference(EntityField[] source, EntityField[] dest)
        {
            List<EntityField> sourceList = new List<EntityField>();
            sourceList.AddRange(source);
            List<EntityField> destList = new List<EntityField>();
            destList.AddRange(dest);

            for (int i = 0; i < sourceList.Count; i++ )
                foreach (EntityField newfield in destList)
                {
                    if (newfield.Name == sourceList[i].Name && newfield.TypeName == sourceList[i].TypeName)
                    {
                        sourceList.RemoveAt(i);
                        i--;
                        break;
                    }
                }

            return sourceList;
        }

       /// <summary>
       /// 
       /// </summary>
       /// <param name="dtSource"></param>
       /// <param name="dr"></param>
       /// <param name="keyName"></param>
       /// <returns></returns>
        private bool IsRowPresent(DataTable dtSource, DataRow dr ,string keyName)
        {
            DataRow[] drColl = dtSource.Select(keyName + "='" + dr[keyName].ToString() + "'");
            if (drColl != null && drColl.Length == 1)
            {
                return true;
            }

            return false;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="entityName"></param>
        /// <param name="keyName"></param>
        private void DeleteOldRows(DataTable dt, string entityName , string keyName)
        {
            DataRow[] deletedRows = dt.Select("", "", DataViewRowState.Deleted);
            string deleteSQL = string.Format("Delete From [{0}] Where [{1}] = '", entityName,keyName);

            foreach (DataRow delRow in deletedRows)
            {
                string deleteSQLDel = deleteSQL;
                deleteSQLDel += delRow[keyName, DataRowVersion.Original].ToString() + "'";
                db.ExecuteNonQuery(CommandType.Text, deleteSQLDel);
            }
        }
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="entityName"></param>
        /// <param name="keyName"></param>
        /// <param name="openPositionList"></param>
        internal int DeleteClosedPosition(string entityName, string keyName, Collection<Guid> openPositionList)
        {
            string deleteSQL = "Delete From [{0}] Where [{1}] NOT IN ({2}) Or [{1}] is null";
            if (openPositionList.Count > 0)
            {
                string ids = string.Empty;
                foreach (Guid id in openPositionList)
                {
                    ids += string.Format("'{0}',", id.ToString());
                }
                ids = ids.Remove(ids.Length - 1, 1);
                deleteSQL = string.Format(deleteSQL, entityName, keyName, ids);
            }
            else
            {
                deleteSQL = string.Format("Delete From [{0}]", entityName);
            }
            return db.ExecuteNonQuery(CommandType.Text, deleteSQL); 
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        internal List<Guid> GetOpenPositionIds()
        {
            List<Guid> returnValue = new List<Guid>();

            DataTable dt = this.GetAllEntities(Constants.Entity.Position);
            if (dt != null)
            {
                foreach (DataRow row in dt.Rows)
                {
                    returnValue.Add(new Guid(row["id"].ToString()));
                }
            }
            return returnValue; 
        }

        public void CleanLocalDB()
        {
            //Drop Entity Tables which are mentioed in Entity Definition Table.
            //Delete Records from Entity and View Definition Table.
            //OfflineDBService should expose this method to modulecontroller from where it will get invoked by clicking menu.
            //Cleaning of DataBase should be followed by Schema Synchrnoization.

            using (TransactionScope tranScope = new TransactionScope(TransactionScopeOption.Suppress))
            {
                //Find entity present in Entity Definition Table
                string deleteSQL = string.Format("SELECT * FROM entityDef");
                string entityName = (string)db.ExecuteScalar(CommandType.Text, deleteSQL);

                //Delete Entity Definition Table
                if (entityName.Length > 0)
                    DeleteEntityDef(entityName);

                // Delete records in View Definition Table
                deleteSQL = string.Format("Delete FROM ViewDef");
                db.ExecuteNonQuery(CommandType.Text, deleteSQL);

                //Delete dead queue entries
                string selectSQL = string.Format("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DeadLetter'");
                int qty = (int)db.ExecuteScalar(CommandType.Text, selectSQL);
                if (qty > 0)
                {
                    deleteSQL = string.Format("Delete FROM DeadLetter");
                    db.ExecuteNonQuery(CommandType.Text, deleteSQL);
                }

                //Delete Requests queue entries
                selectSQL = string.Format("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Requests'");
                qty = (int)db.ExecuteScalar(CommandType.Text, selectSQL);
                if (qty > 0)
                {
                    deleteSQL = string.Format("Delete FROM Requests");
                    db.ExecuteNonQuery(CommandType.Text, deleteSQL);
                }
            }
        }
    }
}
